Hello and welcome.
In this video,
you will learn how to write queries
that access more than one table.
There are several ways to access
multiple tables in the same query.
Namely, using Sub-queries, Implicit JOIN,
and JOIN operators, such as INNER JOIN and OUTER JOIN.
In this video, we'll examine the first two options.
The third option is covered in
more detail in other videos.
Let's consider the employees and
departments tables from a previous video.
The employees table contains
several columns for categories,
such as employee ID,
first name, last name,
and salary to name a few.
The Departments table contains a department ID,
department name, Manager ID, and location ID.
Some sample data from these tables is shown here.
We will utilize these tables
for the examples in this video.
In a previous video,
we learned how to use sub-queries.
Now, let's use sub-queries to work with multiple tables.
If we want to retrieve only the employee records from
the employees table for which
a department ID exists in the departments table,
we can use a sub-query as follows.
Select star from employees,
where department_ID IN,
select department_ID_department from departments.
Here the outer query accesses
the employees table and the sub-query
on the departments table is used for
filtering the result set of the outer query.
Let's say we want to retrieve
only the list of employees from a specific location.
We do not have any location information
in the employees table,
but the departments table has
a column called location ID.
Therefore, we can use a sub-query from
the Departments table as input to
the employee table query as follows.
Select star from employees, where department_ID IN,
select department_ID_department from departments,
where location ID equals L0002.
Now, let's retrieve the department ID and
department name for employees who earn more than $70,000.
To do so, we will need a sub-query on
the employees table to satisfy the salary criteria,
and then feed it as input to an outer query on
the departments table in order to
get the matching department info.
Select department_ID_department department_name
from departments, where department_ID_department IN, select
department_ID from employees where
salary is greater than 70,000.
We can also access multiple tables by
specifying them in the FROM clause of the query.
Consider the example, select
star from employees, departments.
Here we specify two tables in the FROM clause.
This results in a table join,
but note we are not explicitly using the join operator.
The resulting join in this example is called a
full join or Cartesian join,
because every row in the first table
is joined with every row in the second table.
If you examine the results set,
you will see more rows than in both tables individually.
We can use additional operands to limit the result set.
Let's look at an example where we limit the result
set to only rows with matching department IDs.
Select star from employees, departments, where employees
department_ID equals departments,
department_ID_department.
Notice that in the WHERE clause,
we prefix the name of
the column with the name of the table.
This is to fully qualify the column name,
since it's possible that different tables could
have some column names that are exactly the same.
Since the table names can sometimes be long,
we can use shorter aliases for table names as shown here.
Select star from employees E, departments D,
where E department_ID equals D department_ID_department.
Here, we define the alias E for
employees table and D for departments table,
and then use these aliases in the WHERE clause.
If we wanted to see
the department name for each employee,
we would enter the code as follows:
Select
Employee_ID, Department_Name
from employees E, departments D,
where E department_ID equals D
department_ID_department.
Similar to before, the column names in the select clause
can also be prefixed by aliases as shown in the query.
Select E. Employee_ID, D. Department_ID_department,
from employees E. Departments D where
E.Department_ID equals D. Department_ID_department.
In this lesson, we have shown
you how to work with multiple tables
using sub-queries and
implicit joins. Thanks for watching.